using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;
using MyClever.Lib;

namespace MyClever.DefaultPlugins
{
    public class ReadMySQL : MissionPlugin
    {
        [Package(Direction.Input, "Defines the setting to get access to the MySQL database.")]
        enum MySQLCredentials
        {
            [Column(typeof(string), "The host, you like read from.")]
            Host,
            [Column(typeof(string), "The database, you like to read from.")]
            DatabaseName,
            [Column(typeof(string), "The user name to get access to the database.")]
            UserName,
            [Column(typeof(string), "The user password to get access to the database.")]
            UserPassword
        }

        [Package(Direction.Input, "Defines the setting to read from the MySQL database file.")]
        enum MySQLQuery
        {
            [Column(typeof(string), "Defines the table you like to access.")]
            TableName,
            [Column(typeof(string), "Which data you like to read. (example: column1 > 12)",
                DefaultValue="1")]
            Where,
            [Column(typeof(bool), "If true, all read data will be delete!",
                DefaultValue="False")]
            DeleteReadData
        }

        [Package(Direction.Output, "Defines the data, which was loaded from the database.")]
        enum DBData { }

        [Plugin(PluginDevStatus.Production, "Reads data from your favorite table in the expected MySQL database.")]
        public ReadMySQL()
        {
        }

        public override void Initialize()
        {
            
            //This is the first Function called by the host...
            //Put anything needed to start with here first
        }

        public override void Dispose()
        {
            //Put any cleanup code in here for when the program is stopped
        }

        /// <summary>
        /// Generate new Source-content.
        /// </summary>
        protected override void Work()
        {
            DataTableReader mySQLCredentialsPackage = this.GetDataReader(typeof(MySQLCredentials));
            while (mySQLCredentialsPackage.Read())
            {
                this.AddNewLoggingMessage(typeof(MySQLCredentials), String.Format("connect to mysql database: '{0}' from host: {1}", mySQLCredentialsPackage[MySQLCredentials.DatabaseName.ToString()].ToString(), mySQLCredentialsPackage[MySQLCredentials.Host.ToString()].ToString()));

                string connectionString = String.Format("Server={0};Uid={1};Pwd={2};Database={3};", mySQLCredentialsPackage[MySQLCredentials.Host.ToString()].ToString(), mySQLCredentialsPackage[MySQLCredentials.UserName.ToString()].ToString(), mySQLCredentialsPackage[MySQLCredentials.UserPassword.ToString()].ToString(), mySQLCredentialsPackage[MySQLCredentials.DatabaseName.ToString()].ToString());

                MySqlConnection connection = new MySqlConnection(@connectionString);
                connection.Open();

                StringBuilder deleteCommandQuery = new StringBuilder();

                DataTableReader mySQLQueryPackage = this.GetDataReader(typeof(MySQLQuery));
                while (mySQLQueryPackage.Read())
                {
                    string commandText = String.Format("SELECT * FROM {0} WHERE {1}", mySQLQueryPackage[MySQLQuery.TableName.ToString()].ToString(), mySQLQueryPackage[MySQLQuery.Where.ToString()].ToString());

                    MySqlCommand command = new MySqlCommand(commandText, connection);
                    MySqlDataReader reader = command.ExecuteReader();

                    // create schema for output package 'DBData'
                    DataTable schemaTable = reader.GetSchemaTable();
                    List<KeyValuePair<string, Type>> columnDefinitions = new List<KeyValuePair<string, Type>>();
                    foreach (DataRow row in schemaTable.Rows)
                    {
                        columnDefinitions.Add(new KeyValuePair<string, Type>(row["ColumnName"].ToString(), (Type)row["DataType"]));
                    }
                    this.CreateNewSchemaForPackage(typeof(DBData), columnDefinitions);

                    // read content from table
                    while (reader.Read())
                    {             
                        DataRow newRow = this.CreateNewRowFromPackage(typeof(DBData));
                        for (int columnID = 0; columnID < reader.FieldCount; columnID++)
                        {
                            newRow[columnID] = reader[columnID];
                        }
                        this.AddNewRowToPackage(typeof(DBData), newRow);

                        // prepare delete query if needed
                        if (true == Convert.ToBoolean(mySQLQueryPackage[MySQLQuery.DeleteReadData.ToString()]))
                        {
                            string columnValuesQuery = "";
                            foreach (KeyValuePair<string, Type> columnDefinitionOn in columnDefinitions)
                            {
                                columnValuesQuery += " AND " + columnDefinitionOn.Key + " = '" + newRow[columnDefinitionOn.Key].ToString().Replace("'", "''") + "'";
                            }
                            deleteCommandQuery.AppendFormat("DELETE FROM {0} WHERE {1}; ", mySQLQueryPackage[MySQLQuery.TableName.ToString()].ToString(), columnValuesQuery.Substring(4));
                        }
                    }
                    
                    reader.Dispose();
                    reader.Close();

                    // delete read data, if required
                    if (true == Convert.ToBoolean(mySQLQueryPackage[MySQLQuery.DeleteReadData.ToString()]) && deleteCommandQuery.Length > 0)
                    {
                        MySqlCommand deleteCommand = new MySqlCommand(deleteCommandQuery.ToString(), connection);
                        deleteCommand.ExecuteNonQuery();
                    }

                    command.Dispose();
                }

                connection.Close();
                connection.Dispose();
                connection = null;
            }            
        }
    }
}
